﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using ConsumeSys.Orm;

namespace ConsumeSys.DAL
{
    /// <summary>
    /// 用户类
    /// </summary>
    public class UserDAL
    {
        #region 用户操作

        /// <summary>
        /// 返回登录用户
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>User</returns>
        public Model.User GetLoginUser(string username, string password)
        {
            Model.User _userModel = null;
            string sql = string.Format("SELECT * FROM [dbo].[tb_User] WHERE ([u_UserName] = '{0}' or [u_MobliePhone] = '{0}') and [u_Password] = '{1}' and u_IsLock = 0 ", username, password.Jmd5());
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            _userModel = EntityBase.DataTableToModel<Model.User>(dt);
            #region 更新登录时间
            Task.Run(()=> {
                if (_userModel != null && _userModel.id > 0)
                {
                    string strSql = "update tb_User set u_LastLoginTime = getdate() where id =" + _userModel.id;
                    DBHelper.SQLHepler.ExecuteSql(strSql);
                }
            });          
            #endregion
            return _userModel;
        }

        /// <summary>
        /// 返回User
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>User</returns>
        public Model.User GetUser(int id)
        {
            Model.User _userModel = null;
            string sql = string.Format("SELECT * FROM [dbo].[tb_User] WHERE [id] = {0}", id);
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            _userModel = EntityBase.DataTableToModel<Model.User>(dt);
            return _userModel;
        }

        /// <summary>
        /// 返回User
        /// </summary>
        /// <param name="username">用户名</param>
        /// <returns>User</returns>
        public Model.User GetUser(string username)
        {
            Model.User _userModel = null;
            string sql = string.Format("SELECT * FROM [dbo].[tb_User] WHERE [u_UserName] = '{0}'", username);
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            _userModel = EntityBase.DataTableToModel<Model.User>(dt);
            return _userModel;
        }

        /// <summary>
        /// 用户列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="realName">真实姓名</param>
        /// <returns>Paging</returns>
        public Paging<Model.User> GetPagingUser(int pageIndex, int pageSize, string realName)
        {
            #region 查询条件
            string sql = string.Empty;
            if (!string.IsNullOrWhiteSpace(realName))
            {
                sql = " and u_RealName like '%" + realName + "%'";
            }
            #endregion
            Paging<Model.User> _paging = new Paging<Model.User>(pageIndex, pageSize, "tb_User", sql);
            _paging.GetPageingDataList();
            return _paging;
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user">需要添加的用户Model</param>
        /// <returns>受影响的行数</returns>
        public int AddUser(Model.User user)
        {
            string sql = string.Format("SELECT * FROM [dbo].[tb_User] WHERE [u_UserName] = '{0}'", user.u_UserName);
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                throw new Exception("用户名已经存在");
            }

            #region 添加语句
            StringBuilder insertSql = new StringBuilder();
            insertSql.AppendFormat("INSERT INTO [dbo].[tb_User]");
            insertSql.AppendFormat("([u_UserName]");
            insertSql.AppendFormat(",[u_Password]");
            insertSql.AppendFormat(",[u_RealName]");
            insertSql.AppendFormat(",[u_MobliePhone]");
            insertSql.AppendFormat(",[u_Gender]");
            insertSql.AppendFormat(",[u_Email]");
            insertSql.AppendFormat(",[u_IsLock]");
            insertSql.AppendFormat(",[u_UTypeId]");
            insertSql.AppendFormat(",[u_Remark]");
            insertSql.AppendFormat(",[u_RegisterTime]");
            insertSql.AppendFormat(",[u_UpdateTime]");
            insertSql.AppendFormat(",[u_LastLoginTime])");
            insertSql.AppendFormat(" VALUES ");
            insertSql.AppendFormat("('{0}'", user.u_UserName);
            insertSql.AppendFormat(",'{0}'", user.u_Password.Jmd5());
            insertSql.AppendFormat(",'{0}'", user.u_RealName);
            insertSql.AppendFormat(",'{0}'", user.u_MobliePhone);
            insertSql.AppendFormat(",'{0}'", user.u_Gender);
            insertSql.AppendFormat(",'{0}'", user.u_Email);
            insertSql.AppendFormat(",0");
            insertSql.AppendFormat(",2");
            insertSql.AppendFormat(",'{0}'", user.u_Remark);
            insertSql.AppendFormat(",getdate()");
            insertSql.AppendFormat(",getdate()");
            insertSql.AppendFormat(",getdate())");
            #endregion
            int count = DBHelper.SQLHepler.ExecuteSql(insertSql.ToString());
            return count;
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="ids">主键集合</param>
        /// <returns>受影响的行数</returns>
        public int DeleteUser(string ids)
        {
            string sql = "delete from tb_User where id in(" + ids + ")";
            int count = DBHelper.SQLHepler.ExecuteSql(sql);
            return count;
        }

        /// <summary>
        /// 返回用户list
        /// </summary>
        /// <returns>List</returns>
        public List<Model.User> GetUserList()
        {
            string sql = "select * from tb_User";
            DataTable dt = DBHelper.SQLHepler.GetDataTable(sql);
            return EntityBase.DataTableToList<Model.User>(dt);
        }

        #endregion

    }
}
